﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;
namespace DAO
{
    public class PhieuThuePhongDAO : Abstract
    {
        public static bool LapPhieuThuePhong(PhieuThuePhongDTO phieuThuePhong)
        {
            String query = "insert into PhieuThuePhong (MaKhachHang, NgayThue, MaPhong, SoLuongKhach, NgayTra) values ({1},'{2}',{3},{4},'{5}')"
                .Replace("{1}", phieuThuePhong.MaKhachHang.ToString())
                .Replace("{2}", phieuThuePhong.NgayThue.ToString())
                .Replace("{3}", phieuThuePhong.MaPhong.ToString())
                .Replace("{4}", phieuThuePhong.SoLuongKhach.ToString())
                .Replace("{5}", "1/1/1992");// ngay tra = 1/1/1992 tuc la phong chua tra
            return Query(query);
        }

        public static bool SuaPhieuThuePhong(PhieuThuePhongDTO phieuThuePhong)
        {
            String query = "update PhieuThuePhong set MaPhong = '{0}' , SoLuongKhach = {1}, NgayTra = '{3}' where MaPhieuThuePhong = {2}"
                .Replace("{0}", phieuThuePhong.MaPhong.ToString())
                .Replace("{1}", phieuThuePhong.SoLuongKhach.ToString())
                .Replace("{2}", phieuThuePhong.MaPhieuThuePhong.ToString())
                .Replace("{3}", phieuThuePhong.NgayTra.ToShortDateString());

            return Query(query);
        }

        public static bool XoaPhieuThuePhong(int _maPhieuThuePhong)
        {
            string query = "delete from PhieuThuePhong where MaPhieuThuePhong = {0}"
                .Replace("{0}", _maPhieuThuePhong.ToString());
            return Query(query);
        }


        public static List<PhieuThuePhongDTO> LayDSPhieuThuePhong(int maPhieuThuePhong = -1, int maKhachHang = -1, int thang = -1, int nam = -1, int maPhong = -1,int maPhongDeXoa = -1)
        {
            OleDbConnection conn = null;
            List<PhieuThuePhongDTO> listPhieuThuePhong = new List<PhieuThuePhongDTO>();
            try
            {
                conn = GetConnection();
                conn.Open();
            }
            catch
            {
                throw new Exception("Connect error!");
            }
            try
            {
                string query = "select MaPhieuThuePhong, MaKhachHang, NgayThue, MaPhong, SoLuongKhach,NgayTra from PhieuThuePhong ";
                if (maPhieuThuePhong != -1)
                    query = query + "where MaPhieuThuePhong = {0}".Replace("{0}", maPhieuThuePhong.ToString());

                if (thang != -1 && nam != -1)
                    query = query + "where MONTH(NgayTra) = '{2}' AND YEAR(NgayTra) = '{3}'".Replace("{2}", thang.ToString()).Replace("{3}", nam.ToString());
                else if (maKhachHang != -1)
                    query = query + "where MaKhachHang = {1}".Replace("{1}", maKhachHang.ToString());
                else if (maPhong != -1)// ma phong de thanh toan
                    query = query + "where MaPhong = {0} and NgayTra BETWEEN #1/1/1992#  AND #1/1/1992#"
                        .Replace("{0}", maPhong.ToString());
                else if (maPhongDeXoa != -1)//ma phong de xoa day chuyen
                    query = query + "where MaPhong = {0}"
                        .Replace("{0}", maPhongDeXoa.ToString());
                OleDbDataReader rd = new OleDbCommand(query, conn).ExecuteReader();
                while (rd.Read())
                {
                    PhieuThuePhongDTO phieuThuePhong = new PhieuThuePhongDTO();
                    phieuThuePhong.MaPhieuThuePhong = rd.GetInt32(0);
                    phieuThuePhong.MaKhachHang = rd.GetInt32(1);
                    phieuThuePhong.NgayThue = rd.GetDateTime(2);
                    phieuThuePhong.MaPhong = rd.GetInt32(3);
                    phieuThuePhong.SoLuongKhach = rd.GetInt32(4);
                    phieuThuePhong.NgayTra = rd.GetDateTime(5);
                    listPhieuThuePhong.Add(phieuThuePhong);
                }

            }
            catch (Exception e)
            {
                listPhieuThuePhong = null;
            }

            if (conn.State == System.Data.ConnectionState.Open)
                conn.Close();
            return listPhieuThuePhong;
        }
    }
}
